package br.com.evoluy.repositorio;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import br.com.evoluy.entidade.Usuario;
import br.com.evoluy.util.ConnectionFactory;

public class RepositorioProfissional {
	
	List<Usuario> profissionais = new ArrayList<Usuario>();
	
	
	public void salvaProfissional(Usuario usuario)
	{
        Connection con = new ConnectionFactory().getConnection();
		String sql = "insert into usuario"+"(id,ativo,bairro,cep,cidade,conselho,cpf,datanascimento,email,funcao,nivel,nome," +
				     "numero,numeroconselho,psf,rg,endereco,senha,sexo,sobrenome,telefone,uf  )"+
                     "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		try 
		{	
			PreparedStatement stmp = (PreparedStatement) con.prepareStatement(sql);
			stmp.setLong(1, usuario.getId());
			stmp.setString(2, usuario.getAtivo());
			stmp.setString(3, usuario.getBairro());
			stmp.setString(4, usuario.getCep());
			stmp.setString(5, usuario.getCidade());
			stmp.setString(6, usuario.getConselho());
			stmp.setString(7, usuario.getCpf());
			stmp.setString(8, usuario.getDatanascimento());
			stmp.setString(9, usuario.getEmail());
			stmp.setString(10, usuario.getFuncao());
			stmp.setString(11, usuario.getNivel());
			stmp.setString(12, usuario.getNome());
			stmp.setString(13, usuario.getNumero());
			stmp.setString(14, usuario.getNumeroconselho());
			stmp.setString(15, usuario.getPsf());
			stmp.setString(16, usuario.getRg());
			stmp.setString(17, usuario.getEndereco());
			stmp.setString(18, usuario.getSenha());
			stmp.setString(19, usuario.getSexo());
			stmp.setString(20, usuario.getSobrenome());
			stmp.setString(21, usuario.getTelefone());
			stmp.setString(22, usuario.getUf());
			stmp.execute();
			stmp.close();
		} 
		catch (SQLException e) 
		{	
			e.printStackTrace();
		}
		finally
		{
			try 
			{
				con.close();
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
	}
	
	
	public List<Usuario> procurarProfissional()
	{				
		Usuario profissional = null;
		
		String sql = "select * from usuario";
		Connection con = new ConnectionFactory().getConnection();
		
		try 
		{		
			PreparedStatement stmp = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = stmp.executeQuery(sql);
			
			
			while(rs.next())
			{
				profissional = new Usuario();
				profissional.setId(rs.getInt("id"));
				profissional.setNome(rs.getString("nome"));
				profissional.setSobrenome(rs.getString("sobrenome"));
				profissional.setEndereco(rs.getString("endereco"));
				profissional.setBairro(rs.getString("bairro"));
				profissional.setNumero(rs.getString("numero"));
				profissional.setCidade(rs.getString("cidade"));
				profissional.setUf(rs.getString("uf"));
				profissional.setSexo(rs.getString("sexo"));
				profissional.setCep(rs.getString("cep"));
				profissional.setTelefone(rs.getString("telefone"));
				profissional.setRg(rs.getString("rg"));
				profissional.setCpf(rs.getString("cpf"));
				profissional.setDatanascimento(rs.getString("datanascimento"));
				profissional.setNivel(rs.getString("nivel"));
				profissional.setEmail(rs.getString("email"));
				profissional.setSenha(rs.getString("senha"));
				profissional.setAtivo(rs.getString("ativo"));
				profissional.setFuncao(rs.getString("funcao"));
				profissional.setPsf(rs.getString("psf"));
				profissional.setConselho(rs.getString("conselho"));
				profissional.setNumeroconselho(rs.getString("numeroconselho"));
				profissionais.add(profissional);
			}
			
			rs.close();
			stmp.close();

		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try 
			{	
				con.close();
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}		
	  return profissionais;		
	}
	
	
	public boolean procurarConselho(String conselho, String numeroConselho)
	{				
		boolean retorno = false;
		
		String sql = "select * from usuario";
		Connection con = new ConnectionFactory().getConnection();
		
		try 
		{		
			PreparedStatement stmp = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = stmp.executeQuery(sql);
			
			
			while(rs.next())
			{
			  if(rs.getString("conselho").equals(conselho) && rs.getString("numeroconselho").equals(numeroConselho))
			  {
				  retorno = true;
				  break;
			  }  
			}
			
			rs.close();
			stmp.close();

		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try 
			{	
				con.close();
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}		
	  return retorno;		
	}
	
	
	public Usuario procurarProfissional(String conselho, String numeroConselho)
	{				
		Usuario profissional = null;
		
		String sql = "select * from usuario";
		Connection con = new ConnectionFactory().getConnection();
		
		try 
		{		
			PreparedStatement stmp = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = stmp.executeQuery(sql);
			
			
			while(rs.next())
			{
			  if(rs.getString("conselho").equals(conselho) && rs.getString("numeroconselho").equals(numeroConselho))
			  {
				    profissional = new Usuario();
					profissional.setId(rs.getInt("id"));
					profissional.setNome(rs.getString("nome"));
					profissional.setSobrenome(rs.getString("sobrenome"));
					profissional.setEndereco(rs.getString("endereco"));
					profissional.setBairro(rs.getString("bairro"));
					profissional.setNumero(rs.getString("numero"));
					profissional.setCidade(rs.getString("cidade"));
					profissional.setUf(rs.getString("uf"));
					profissional.setSexo(rs.getString("sexo"));
					profissional.setCep(rs.getString("cep"));
					profissional.setTelefone(rs.getString("telefone"));
					profissional.setRg(rs.getString("rg"));
					profissional.setCpf(rs.getString("cpf"));
					profissional.setDatanascimento(rs.getString("datanascimento"));
					profissional.setNivel(rs.getString("nivel"));
					profissional.setEmail(rs.getString("email"));
					profissional.setSenha(rs.getString("senha"));
					profissional.setAtivo(rs.getString("ativo"));
					profissional.setFuncao(rs.getString("funcao"));
					profissional.setPsf(rs.getString("psf"));
					profissional.setConselho(rs.getString("conselho"));
					profissional.setNumeroconselho(rs.getString("numeroconselho"));
				    break;
			  }  
			}
			
			rs.close();
			stmp.close();

		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		finally
		{
			try 
			{	
				con.close();
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}		
	  return profissional;		
	}
	
	
	public void updateProfissional(Usuario usuario) 
	{

		Connection con = new ConnectionFactory().getConnection();
		
		try
		{
			PreparedStatement update = (PreparedStatement) con.prepareStatement
			("UPDATE usuario SET nome = ?, sobrenome = ?, sexo = ?, endereco = ?, bairro = ?, numero = ?," +
			 "cidade = ?, uf = ?, cep = ?, telefone = ?, rg = ?, cpf = ?, datanascimento = ?, nivel = ?, email = ?," +
			 "ativo = ?, psf = ?, conselho = ?, numeroconselho = ? WHERE id = ?");
			
			update.setString(1, usuario.getNome());
			update.setString(2, usuario.getSobrenome());
			update.setString(3, usuario.getSexo());
			update.setString(4, usuario.getEndereco());
			update.setString(5, usuario.getBairro());
			update.setString(6, usuario.getNumero());
			update.setString(7, usuario.getCidade());
			update.setString(8, usuario.getUf());
			update.setString(9, usuario.getCep());
			update.setString(10,usuario.getTelefone());
			update.setString(11,usuario.getRg());
			update.setString(12,usuario.getCpf());
			update.setString(13,usuario.getDatanascimento());
			update.setString(14,usuario.getNivel());
			update.setString(15,usuario.getEmail());
			update.setString(16,usuario.getAtivo());
			update.setString(17,usuario.getPsf());
			update.setString(18,usuario.getConselho());
			update.setString(19,usuario.getNumeroconselho());
			update.setString(20, Integer.toString(usuario.getId()));
			update.executeUpdate();
			update.close();
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try 
			{	
				con.close();
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}		
		
	}

}
